package com.robot.orders.dao;

import com.robot.orders.pojo.Orders;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * orders数据访问接口
 *
 * @author wuyunbin
 */
public interface OrdersDao extends JpaRepository<Orders, String>, JpaSpecificationExecutor<Orders> {
    /**
     * orders根据memberId查找总订单
     *
     * @param memberId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where member_id=? and status=1 order by created_at desc ", nativeQuery = true)
     Page<Orders> findOrdersByMemberIdAndTotal(String memberId, Pageable pageable);

    /**
     * 根据memberId查找会员名下所有未删除的订单并分页
     *
     * @param memberId
     * @param pageable
     * @return
     */
     Page<Orders> findAllByMemberIdAndDeletedAtIsNullOrderByCreatedAtDesc(String memberId, Pageable pageable);


    /**
     * 根据memberID和status查找会员名下所有未删除的订单并分页
     *
     * @param memberId
     * @param status
     * @param pageable
     * @return
     */
     Page<Orders> findAllByMemberIdAndStatusAndDeletedAtIsNullOrderByCreatedAtDesc(String memberId, String status, Pageable pageable);

    /**
     * 通过id和memberId查询某一订单
     *
     * @param id
     * @param memberId
     * @return
     */
     Orders findByIdAndMemberId(String id, String memberId);


    /**
     * 根据memberId查找会员累计支付成功的订单数
     *
     * @param memberId
     * @return
     */
    @Query(value = "select count(*) from orders.orders where member_id=? and status=1", nativeQuery = true)
     String findCountByMemberId(String memberId);

    /**
     * 根据memberId查找会员累计消费金额
     *
     * @param memberId
     * @return
     */
    @Query(value = "select sum(pay_amount) from orders.orders where member_id=? and status=1", nativeQuery = true)
     String findPayAmountByMemberId(String memberId);

    /**
     * 根据memberId和storeId查找会员累计消费金额
     *
     * @param memberId
     * @param storeId
     * @return
     */
    @Query(value = "select sum(pay_amount) from orders.orders where member_id=? and store_id=? and status=1", nativeQuery = true)
     String findPayAmountByMemberIdAndStoreId(String memberId, String storeId);

    /**
     * 根据memberId查找会员累计节省金额
     *
     * @param memberId
     * @return
     */
    @Query(value = "select sum(discount_amount) from orders.orders where member_id=? and status=1", nativeQuery = true)
     String findDiscountAmountByMemberId(String memberId);

    /**
     * 根据storeId查找本月累计支付成功的订单数
     *
     * @param storeId
     * @return
     */
    @Query(value = "SELECT count(*) FROM orders.orders WHERE store_id=? and status=1 and DATE_FORMAT( created_at,'%Y%m') = DATE_FORMAT(CURDATE( ) ,'%Y%m')", nativeQuery = true)
     String findThisMonthCountByStoreId(String storeId);

    /**
     * 根据storeId查找累计消费金额
     *
     * @param storeId
     * @return
     */
    @Query(value = "select sum(pay_amount) from orders.orders where store_id=? and status=1", nativeQuery = true)
     String findPayAmountByStoreId(String storeId);

    /**
     * 根据storeId查找今天订单
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where store_id=? and status=1 and TO_DAYS(NOW()) - TO_DAYS(created_at) =0", nativeQuery = true)
     Page<Orders> findAllByStoreIdAndToday(String storeId, Pageable pageable);

    /**
     * 根据storeId查找昨天订单
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.sub_orders where store_id=? and status=1 and TO_DAYS(NOW()) - TO_DAYS(created_at) =1", nativeQuery = true)
     Page<Orders> findAllByStoreIdAndYesterday(String storeId, Pageable pageable);
    /**
     * 根据storeId查找最近7日订单
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where store_id=? and status=1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(created_at)", nativeQuery = true)
     Page<Orders> findAllByStoreIdAndWeek(String storeId, Pageable pageable);

    /**
     * 根据storeId查找总订单
     *
     * @param storeId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where store_id=? and status=1", nativeQuery = true)
     Page<Orders> findAllByStoreIdAndTotal(String storeId, Pageable pageable);
    /**
     * 根据agentId查找今天订单
     *
     * @param agentId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where agent_id=? and status=1 and TO_DAYS(NOW()) - TO_DAYS(created_at) =0", nativeQuery = true)
     Page<Orders> findAllByAgentIdAndToday(String agentId, Pageable pageable);

    /**
     * 根据agentId查找昨天订单
     *
     * @param agentId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where agent_id=? and status=1 and TO_DAYS(NOW()) - TO_DAYS(created_at) =1", nativeQuery = true)
     Page<Orders> findAllByAgentIdAndYesterday(String agentId, Pageable pageable);

    /**
     * 根据agentId查找最近7日订单
     *
     * @param agentId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where agent_id=? and status=1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(created_at)", nativeQuery = true)
     Page<Orders> findAllByAgentIdAndWeek(String agentId, Pageable pageable);

    /**
     * 根据agentId查找总订单
     *
     * @param agentId
     * @param pageable
     * @return
     */
    @Query(value = "select * from orders.orders where agent_id=? and status=1", nativeQuery = true)
     Page<Orders> findAllByAgentIdAndTotal(String agentId, Pageable pageable);

    /**
     * 根据agentId和createdAt查找总订单
     *
     * @param agentId
     * @param createdAt
     * @return
     */
    @Query(value = "select * from orders.orders where agent_id=? and to_days(created_at)=to_days(?)", nativeQuery = true)
     List<Orders> findAllByAgentIdAndCreatedAt(String agentId, String createdAt);

    @Query(value="select count(id) as total,count(if(status='0',1,null)) as notpay ,count(if(status='1',1,null)) as paid,count(if(status='2',1,null)) as closed from orders.orders where member_id=? and deleted_at is null", nativeQuery = true)
    List<Integer[]> countAllStatusOrders(String membrId);
}
